﻿#nullable enable

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using ClosedXML.Excel;
using PHMEE.CommonUtils;
using Volo.Abp;

namespace PHMEE.EnergyEfficiency;

public class ReportAppService : PHMEEAppService, IReportAppService
{
    private readonly VesselEeConfigDomainService _vesselEeConfigDomainService;
    private readonly ILogAbstractRepository _logAbstractRepository;
    private readonly LogAbstractDomainService _logAbstractDomainService;
    private readonly ICiiReductionFactorRepository _ciiReductionFactorRepository;
    private readonly FuelConsumptionCalculator _fuelConsumptionCalculator;

    public ReportAppService(
        VesselEeConfigDomainService vesselEeConfigDomainService,
        ILogAbstractRepository logAbstractRepository,
        LogAbstractDomainService logAbstractDomainService,
        ICiiReductionFactorRepository ciiReductionFactorRepository,
        FuelConsumptionCalculator fuelConsumptionCalculator)
    {
        _vesselEeConfigDomainService = vesselEeConfigDomainService;
        _logAbstractRepository = logAbstractRepository;
        _logAbstractDomainService = logAbstractDomainService;
        _ciiReductionFactorRepository = ciiReductionFactorRepository;
        _fuelConsumptionCalculator = fuelConsumptionCalculator;
    }

    private static string GetTemplatePath(string templateFileName)
    {
        var assemblyPath = Assembly.GetExecutingAssembly().Location;
        var currentDirectory = Path.GetDirectoryName(assemblyPath);
        if (currentDirectory == null)
        {
            throw new Exception("currentDirectory is null");
        }
        
        var templatePath = Path.Combine(currentDirectory, "EnergyEfficiency", "Report", "Templates", templateFileName);

        return templatePath;
    }

    public async Task<MemoryStream> ExportIMODCSReportAsync(Guid vesselId, DateTime startDate,
        DateTime endDate)
    {
        var vesselEeConfig = await _vesselEeConfigDomainService.GetByVesselIdAsync(vesselId);
        if (vesselEeConfig is null)
        {
            throw new UserFriendlyException(L[PHMEEEnergyEfficiencyErrorCodes.VesselConfigNotFound],
                PHMEEEnergyEfficiencyErrorCodes.VesselConfigNotFound);
        }

        var (startDateTime, endDateTime) = QueryDateTimeUtils.ProcessForDateRangeQuery(startDate, endDate);
        var yearNumber = startDateTime.Year;

        // if (year >= TimeUtils.ThisYear(DateTimeOffset.UtcNow).Year)
        // {
        //     throw new UserFriendlyException(L[PHMEEEnergyEfficiencyErrorCodes.YearMustBeBeforeCurrentYear],
        //         PHMEEEnergyEfficiencyErrorCodes.YearMustBeBeforeCurrentYear);
        // }
        
        var templatePath = GetTemplatePath("IMO_DCS_template.xlsx");

        await using var templateFileStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        using var workbook = new XLWorkbook(templateFileStream);
        var worksheet = workbook.Worksheet(1);

        // var startDate = TimeUtils.ThisYear(year);
        // var endDate = startDate.AddYears(1);

        // 船舶名称
        worksheet.Cell("C3").Value = vesselEeConfig.Name;

        // 开始结束日期用用户输入的日期，而不是转为查询日期段之后的日期
        // Start Date
        // worksheet.Cell("C4").Value = $"01/01/{yearNumber}";
        worksheet.Cell("C4").Value = startDate.ToString("dd/MM/yyyy");
        // End Date
        // worksheet.Cell("C5").Value = $"31/12/{yearNumber}";
        worksheet.Cell("C5").Value = endDate.ToString("dd/MM/yyyy");

        worksheet.Cell("C6").Value = vesselEeConfig.IMO.ToString();
        worksheet.Cell("C7").Value = vesselEeConfig.FlagState;
        worksheet.Cell("C8").Value = vesselEeConfig.ManagementCompany;
        worksheet.Cell("C9").Value = vesselEeConfig.VesselType.GetDescription();
        worksheet.Cell("C10").Value = vesselEeConfig.GT.ToString("0");
        worksheet.Cell("C11").Value = vesselEeConfig.NT.ToString("0");
        worksheet.Cell("C12").Value = vesselEeConfig.DWT.ToString("0");
        worksheet.Cell("C13").Value = vesselEeConfig.EEDI.ToString("0.00");
        worksheet.Cell("C14").Value = vesselEeConfig.EEXI.ToString("0.00");
        worksheet.Cell("C15").Value = vesselEeConfig.IceClass.GetDescription();
        worksheet.Cell("C16").Value = vesselEeConfig.MainPropulsionPowerOutput;
        worksheet.Cell("C17").Value = vesselEeConfig.AuxiliaryEnginesPowerOutput;

        // 时间段内的 LogAbstracts
        var logAbstracts = await _logAbstractRepository.GetListOpenClosedAsync(vesselId, startDateTime, endDateTime);

        // 航程
        float totalDistance = logAbstracts.Sum(logAbstract => logAbstract.Distance);
        worksheet.Cell("C18").Value = totalDistance.ToString("0.00");

        var voyages = await _logAbstractDomainService.GetVoyagesAsync(logAbstracts, vesselId);
        // 航行时长
        worksheet.Cell("C19").Value = voyages.GetTotalHours().ToString("0.00");

        // 油品油耗
        // float hoursUnderway = 0f;
        float mgoAndMdoConsumption = logAbstracts.GetTotalConsumptionByFuelTypes(FuelTypeHelper.GetFuelTypesForIMODCSReport(FuelType.MGO));
        float lfoConsumption = logAbstracts.GetTotalConsumptionByFuelTypes(FuelTypeHelper.GetFuelTypesForIMODCSReport(FuelType.LFO));
        float hfoConsumption = logAbstracts.GetTotalConsumptionByFuelTypes(FuelTypeHelper.GetFuelTypesForIMODCSReport(FuelType.HFO));
        float lpgPConsumption = logAbstracts.GetTotalConsumptionByFuelTypes(FuelTypeHelper.GetFuelTypesForIMODCSReport(FuelType.LPGP));
        float lpgBConsumption = logAbstracts.GetTotalConsumptionByFuelTypes(FuelTypeHelper.GetFuelTypesForIMODCSReport(FuelType.LPGB));
        float lngConsumption = logAbstracts.GetTotalConsumptionByFuelTypes(FuelTypeHelper.GetFuelTypesForIMODCSReport(FuelType.LNG));
        float methanolConsumption = logAbstracts.GetTotalConsumptionByFuelTypes(FuelTypeHelper.GetFuelTypesForIMODCSReport(FuelType.Methanol));
        float ethanolConsumption = logAbstracts.GetTotalConsumptionByFuelTypes(FuelTypeHelper.GetFuelTypesForIMODCSReport(FuelType.Ethanol));
        worksheet.Cell("C20").Value = mgoAndMdoConsumption.ToString("0.00");
        worksheet.Cell("C21").Value = lfoConsumption.ToString("0.00");
        worksheet.Cell("C22").Value = hfoConsumption.ToString("0.00");
        worksheet.Cell("C23").Value = lpgPConsumption.ToString("0.00");
        worksheet.Cell("C24").Value = lpgBConsumption.ToString("0.00");
        worksheet.Cell("C25").Value = lngConsumption.ToString("0.00");
        worksheet.Cell("C26").Value = methanolConsumption.ToString("0.00");
        worksheet.Cell("C27").Value = ethanolConsumption.ToString("0.00");

        // Applicable CII CII数值单位表达形式
        worksheet.Cell("C31").Value = vesselEeConfig.ApplicableCII?.GetDescription() ?? "";

        // CII 折减系数
        var z = await _ciiReductionFactorRepository.GetByYearAsync(yearNumber);
        if (z is null)
        {
            throw new UserFriendlyException(L[PHMEEEnergyEfficiencyErrorCodes.CIIReductionFactorNotFound],
                    PHMEEEnergyEfficiencyErrorCodes.CIIReductionFactorNotFound)
                .WithData("Year", yearNumber);
        }

        var capacity = VesselEeConfigDomainService.GetCapacity(vesselEeConfig.VesselType, vesselEeConfig.DWT,
            vesselEeConfig.GT);
        // Required annual operation CII 船舶要求的年度 CII 数值 见公式6.2：Required CII
        var requiredAnnualOperationCII = CIIService.RequiredCII(vesselEeConfig.VesselType,
            vesselEeConfig.DWT, vesselEeConfig.GT, z.Z);
        worksheet.Cell("C32").Value = requiredAnnualOperationCII.ToString("0.00");

        // Attained annual operation CII before any correction（g CO2/t·nm） 年度 CII（未修正）数值 见公式2.2：attained CII（用该年中每日的数据计算)
        var ciiUnadjusted = GetCIIUnadjusted(logAbstracts, z, vesselEeConfig, capacity);
        worksheet.Cell("C33").Value = ciiUnadjusted.CII.HasValue ? ciiUnadjusted.CII.Value.ToString("0.00") : "";

        // Attained annual operation CII （g CO2/t·nm） 显示年度 CII（已修正）数值 见公式2.3：CII ship（用该年中每日的数据计算)
        var ciiAdjusted = await GetCIIAdjusted(vesselId, logAbstracts, z, vesselEeConfig, capacity, yearNumber);
        worksheet.Cell("C34").Value = ciiAdjusted.CII.HasValue ? ciiAdjusted.CII.Value.ToString("0.00") : "";

        // CII 评级
        worksheet.Cell("C35").Value = ciiAdjusted.Grade?.ToString() ?? "-";

        // EEOI
        var eeoi = LogAbstractFormulas.EEOINullable(logAbstracts, vesselEeConfig.FuelTypes, vesselEeConfig.VesselType);
        worksheet.Cell("C36").Value = eeoi.HasValue ? eeoi.Value.ToString("0.00") : "";
        // if (annualEEOI.EEOI.HasValue)
        // {
        //     worksheet.Cell("C35").Value = annualEEOI.EEOI.Value.ToString("00.##E+0", CultureInfo.InvariantCulture); // 转为科学计数法，保留小数点前后两位小数
        // }
        // else
        // {
        //     worksheet.Cell("C35").Value = "-";
        // }

        var stream = new MemoryStream();
        workbook.SaveAs(stream);
        stream.Position = 0;

        return stream;
    }

    private CIIWithGradeDto GetCIIUnadjusted(List<LogAbstract> logAbstracts, CiiReductionFactor z,
        VesselEeConfig vesselEeConfig, float capacity)
    {
        var cii = LogAbstractFormulas.CIINullable(logAbstracts, capacity, vesselEeConfig.FuelTypes);

        CIIGrade? ciiGrade = cii is null
            ? null
            : CIIService.DetermineCIIGrade(cii.Value, vesselEeConfig.VesselType, vesselEeConfig.DWT, vesselEeConfig.GT,
                z.Z);

        return new CIIWithGradeDto(cii, ciiGrade);
    }

    private async Task<CIIWithGradeDto> GetCIIAdjusted(Guid vesselId, List<LogAbstract> logAbstracts,
        CiiReductionFactor z,
        VesselEeConfig vesselEeConfig, float capacity, int yearNumber)
    {
        // 航次
        var voyages = await _logAbstractDomainService.GetVoyagesAsync(logAbstracts, vesselId);

        // 调整 log abstract
        var adjustedLogAbstracts =
            await _logAbstractDomainService.GetAdjustedLogAbstractsByTimeRangeAsync(logAbstracts, vesselId);

        // sts log abstracts
        // var stsLogAbstracts = LogAbstractDomainService.GetStsLogAbstracts(voyages);
        var stsLogAbstracts = voyages.GetSTSLogAbstracts();

        var yi = LogAbstractFormulas.GetYi(yearNumber);

        var cii = LogAbstractFormulas.CIICorrectNullable(logAbstracts, adjustedLogAbstracts, stsLogAbstracts, yi,
            capacity, vesselEeConfig.Fi, vesselEeConfig.Fm, vesselEeConfig.Fc, vesselEeConfig.FiVSE,
            vesselEeConfig.FtTanker, vesselEeConfig.FuelTypes);

        CIIGrade? ciiGrade = cii is null
            ? null
            : CIIService.DetermineCIIGrade(cii.Value, vesselEeConfig.VesselType, vesselEeConfig.DWT, vesselEeConfig.GT,
                z.Z);

        return new CIIWithGradeDto(cii, ciiGrade);
    }

    public async Task<MemoryStream> ExportMRVEUReportAsync(Guid vesselId, DateTime startDateTime, DateTime endDateTime)
    {
        var vesselEeConfig = await _vesselEeConfigDomainService.GetByVesselIdAsync(vesselId);
        if (vesselEeConfig is null)
        {
            throw new UserFriendlyException(L[PHMEEEnergyEfficiencyErrorCodes.VesselConfigNotFound],
                PHMEEEnergyEfficiencyErrorCodes.VesselConfigNotFound);
        }

        // var dateRange = new DateTimeRange(startDateTime, endDateTime);
        var (start, end) = QueryDateTimeUtils.ProcessForDateRangeQuery(startDateTime, endDateTime);

        // var yearNumber = _cbmTimeProvider.ValidFormatPastYear(year, YearDefault.PastYear);
        // var (startDate, endDate) = CBMTimeProvider.GetUtcDateRangeByYear(yearNumber);

        var assemblyPath = Assembly.GetExecutingAssembly().Location;
        var currentDirectory = Path.GetDirectoryName(assemblyPath);
        if (currentDirectory == null)
        {
            throw new Exception("current directory is null");
        }

        var templatePath = GetTemplatePath("MRV_EU_template.xlsx");
        
        await using var templateFileStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        using var workbook = new XLWorkbook(templateFileStream);
        var worksheet = workbook.Worksheet(1);

        // 船舶名称
        worksheet.Cell("D4").Value = vesselEeConfig.Name;
        // IMO 号
        worksheet.Cell("D5").Value = vesselEeConfig.IMO.ToString();
        // 开始结束日期用用户输入的日期，而不是转为查询日期段之后的日期
        // 该报表的数据日期范围
        worksheet.Cell("D6").Value = $"{startDateTime:dd/MM/yyyy} - {endDateTime:dd/MM/yyyy}";
        // 船舶类型
        worksheet.Cell("D9").Value = vesselEeConfig.VesselType.GetDescription();
        // 冰级
        worksheet.Cell("D10").Value = vesselEeConfig.IceClass.GetDescription();

        if (vesselEeConfig.VesselType is not VesselType.ContainerShip)
        {
            worksheet.Cell("D12").Value = "N/A";
        }

        // EEDI
        worksheet.Cell("D13").Value = vesselEeConfig.EEDI == 0f ? "N/A" : vesselEeConfig.EEDI.ToString("0.00");
        // EEXI
        worksheet.Cell("D14").Value = vesselEeConfig.EEXI == 0f ? "N/A" : vesselEeConfig.EEXI.ToString("0.00");

        // 船公司
        worksheet.Cell("D24").Value = vesselEeConfig.ManagementCompany;

        // 流量计配置
        var flowMeterConfig = await _fuelConsumptionCalculator.GetFlowMeterConfigAsync(vesselId);
        
        // 主机有进油流量计，则认为主机使用流量计监测
        worksheet.Cell("D61").Value = flowMeterConfig.IsMeBeMonitoredByFlowMeter()
            ? "Method C: Flow meters for applicable combustion processes"
            : "N/A";
        // 副机有进油流量计，则认为副机使用流量计监测
        worksheet.Cell("D62").Value = flowMeterConfig.IsAeBeMonitoredByFlowMeter()
            ? "Method C: Flow meters for applicable combustion processes"
            : "N/A";
        // 锅炉有进油流量计，则认为锅炉使用流量计监测
        worksheet.Cell("D63").Value = flowMeterConfig.IsBoilerBeMonitoredByFlowMeter()
            ? "Method C: Flow meters for applicable combustion processes"
            : "N/A";
        // 其余耗油设备均不加装流量计，所以均为 N/A，若将来存在加装流量计的情况，则需修改对应的功能
        worksheet.Cell("D64").Value = "N/A";
        worksheet.Cell("D65").Value = "N/A";
        worksheet.Cell("D66").Value = "N/A";
        worksheet.Cell("D67").Value = "N/A";
        worksheet.Cell("D68").Value = "N/A";

        #region 计算气体排放需要的参数

        float gwpCO2 = 1.0f;
        float gwpN2O = 265.0f;
        float gwpCH4 = 28.0f;

        // 如该船有 N2O emission factor 为 TBM（尚未测量）的项的燃油，则不计算 totalAggregatedEmissionsOfN2OEmission
        var hasN2oTbmFuel = vesselEeConfig.FuelTypes.Intersect(FuelTypeHelper.N2O_EF_TBM_FuelTypes).Any();
        // 如该船有 CH4 emission factor 为 TBM（尚未测量）的项的燃油，则不计算 totalAggregatedEmissionsOfCH4Emission
        var hasCh4TbmFuel = vesselEeConfig.FuelTypes.Intersect(FuelTypeHelper.CH4_EF_TBM_FuelTypes).Any();

        #endregion 计算气体排放需要的参数
        
        // 燃料消耗和 CO2 排放
        var logAbstracts = await _logAbstractRepository.GetListOpenClosedAsync(vesselId, start, end);
        
        // i0 欧盟航次
        var mrvVoyages = (await _logAbstractDomainService.GetVoyagesAsync(logAbstracts, vesselId)).Where(v => v.IsMRV)
            .ToList();
        
        // TODO: 先划分好 i1、i2、i3、i4，相加即是 i0

        #region i1 成员国港口间航次
        
        var i1MrvPortToMrvPortVoyages = mrvVoyages.Where(v => v.StartDepartureLogAbstract is not null &&
                                                            v.EndDepartureLogAbstract is not null
                                                            && v.StartDepartureLogAbstract.Event ==
                                                            LogAbstractEventType.DepartureEU &&
                                                            v.EndDepartureLogAbstract.Event ==
                                                            LogAbstractEventType.DepartureEU).ToList();
        // i1 (离港，到港] 的 LogAbstract - i1 到港是 ArrivalEU
        var i1MrvPortToMrvPortVoyagesSailingLogAbstracts = 
            GetSailingLogAbstractsFromVoyages(i1MrvPortToMrvPortVoyages, [LogAbstractEventType.ArrivalEU]);
        
        // i1 (离港，到港] 的各种油耗
        var (i1HfoConsumption, i1LsfoConsumption, i1UlsfoConsumption, i1VlsfoConsumption, i1LfoConsumption, i1MdoMgoConsumption, i1LngConsumption, i1LpgButaneConsumption, i1LpgPropaneConsumption, i1MethanolConsumption) 
            = GetAllFuelTypeFuelConsumptionFromLogAbstracts(i1MrvPortToMrvPortVoyagesSailingLogAbstracts);
        
        // i1 (离港，到港] 的各种气体排放
        (
            float i1TotalAggregatedEmissionsOfCO2EmissionMrvPortToMrvPort,
            float i1TotalAggregatedEmissionsOfN2OEmissionMrvPortToMrvPort,
            float i1TotalAggregatedEmissionsOfCH4EmissionMrvPortToMrvPort
        ) = CalculateTotalAggregatedEmissions(i1MrvPortToMrvPortVoyagesSailingLogAbstracts, vesselEeConfig, hasN2oTbmFuel,
            hasCh4TbmFuel);

        worksheet.Cell("D129").Value = i1TotalAggregatedEmissionsOfCO2EmissionMrvPortToMrvPort.ToString("0.00");
        worksheet.Cell("D130").Value = hasN2oTbmFuel ? "N/A" : i1TotalAggregatedEmissionsOfN2OEmissionMrvPortToMrvPort.ToString("0.00");
        worksheet.Cell("D131").Value = hasCh4TbmFuel ? "N/A" : i1TotalAggregatedEmissionsOfCH4EmissionMrvPortToMrvPort.ToString("0.00");
        
        #endregion i1 成员国港口间航次
        
        #region i2 驶离成员国港口航次
        
        var i2MrvPortToNonMrvPortVoyages = mrvVoyages.Where(v => v.StartDepartureLogAbstract is not null &&
                                                               v.EndDepartureLogAbstract is not null
                                                               && v.StartDepartureLogAbstract.Event ==
                                                               LogAbstractEventType.DepartureEU &&
                                                               v.EndDepartureLogAbstract.Event !=
                                                               LogAbstractEventType.DepartureEU).ToList();
        
        // 不包含靠港期间的油耗量，即油耗的区间为（离港，到港]，到港事件不包含 ArrivalEU
        List<LogAbstract> i2MrvPortToNonMrvPortVoyagesLogAbstracts = 
            GetSailingLogAbstractsFromVoyages(i2MrvPortToNonMrvPortVoyages, LogAbstractEventTypeHelper.ArrivalEventsWithoutEU);
        
        // i2 (离港，到港] 的各种油耗
        var (i2HfoConsumption, i2LsfoConsumption, i2UlsfoConsumption, i2VlsfoConsumption, i2LfoConsumption, i2MdoMgoConsumption, i2LngConsumption, i2LpgButaneConsumption, i2LpgPropaneConsumption, i2MethanolConsumption) 
            = GetAllFuelTypeFuelConsumptionFromLogAbstracts(i2MrvPortToNonMrvPortVoyagesLogAbstracts);

        // i2 (离港，到港] 的各种气体排放
        (
            float i2TotalAggregatedEmissionsOfCO2EmissionMrvPortToNonMrvPort,
            float i2TotalAggregatedEmissionsOfN2OEmissionMrvPortToNonMrvPort,
            float i2TotalAggregatedEmissionsOfCH4EmissionMrvPortToNonMrvPort
        ) = CalculateTotalAggregatedEmissions(i2MrvPortToNonMrvPortVoyagesLogAbstracts, vesselEeConfig, hasN2oTbmFuel,
            hasCh4TbmFuel);

        worksheet.Cell("D133").Value = i2TotalAggregatedEmissionsOfCO2EmissionMrvPortToNonMrvPort.ToString("0.00");
        worksheet.Cell("D134").Value = hasN2oTbmFuel ? "N/A" : i2TotalAggregatedEmissionsOfN2OEmissionMrvPortToNonMrvPort.ToString("0.00");
        worksheet.Cell("D135").Value = hasCh4TbmFuel ? "N/A" : i2TotalAggregatedEmissionsOfCH4EmissionMrvPortToNonMrvPort.ToString("0.00");

        #endregion i2 驶离成员国港口航次
        
        #region i3 到达成员国港口航次

        var i3NonMrvPortToMrvPortVoyages = mrvVoyages.Where(v => v.StartDepartureLogAbstract is not null &&
                                                               v.EndDepartureLogAbstract is not null
                                                               && v.StartDepartureLogAbstract.Event !=
                                                               LogAbstractEventType.DepartureEU &&
                                                               v.EndDepartureLogAbstract.Event ==
                                                               LogAbstractEventType.DepartureEU).ToList();
        
        // 不包含靠港期间的油耗量，即油耗的区间为（离港，到港]，到港事件为 ArrivalEU
        List<LogAbstract> i3NonMrvPortToMrvPortVoyagesLogAbstracts = 
            GetSailingLogAbstractsFromVoyages(i3NonMrvPortToMrvPortVoyages, [LogAbstractEventType.ArrivalEU]);
        
        // i3 (离港，到港] 的各种油耗
        var (i3HfoConsumption, i3LsfoConsumption, i3UlsfoConsumption, i3VlsfoConsumption, i3LfoConsumption, i3MdoMgoConsumption, i3LngConsumption, i3LpgButaneConsumption, i3LpgPropaneConsumption, i3MethanolConsumption) 
            = GetAllFuelTypeFuelConsumptionFromLogAbstracts(i3NonMrvPortToMrvPortVoyagesLogAbstracts);
        
        // i3 (离港，到港] 的各种气体排放
        (
            float i3TotalAggregatedEmissionsOfCO2EmissionNonMrvPortToMrvPort,
            float i3TotalAggregatedEmissionsOfN2OEmissionNonMrvPortToMrvPort,
            float i3TotalAggregatedEmissionsOfCH4EmissionNonMrvPortToMrvPort
        ) = CalculateTotalAggregatedEmissions(i3NonMrvPortToMrvPortVoyagesLogAbstracts, vesselEeConfig, hasN2oTbmFuel,
            hasCh4TbmFuel);
        
        worksheet.Cell("D137").Value = i3TotalAggregatedEmissionsOfCO2EmissionNonMrvPortToMrvPort.ToString("0.00");
        worksheet.Cell("D138").Value = hasN2oTbmFuel ? "N/A" : i3TotalAggregatedEmissionsOfN2OEmissionNonMrvPortToMrvPort.ToString("0.00");
        worksheet.Cell("D139").Value = hasCh4TbmFuel ? "N/A" : i3TotalAggregatedEmissionsOfCH4EmissionNonMrvPortToMrvPort.ToString("0.00");

        #endregion i3 到达成员国港口航次
        
        #region i4 欧盟港口停靠

        // 欧盟港口停靠，即油耗的区间为（到欧盟港，离欧盟港]
        // 取 EndDepartureLogAbstract 为 DepartureEU 的 Voyage，这部分 Voyage 包含了停靠在欧盟港期间的 LogAbstract
        // 也就是说要取 i1 成员国港口间航次 和 i3 到达成员国港口航次 的 LogAbstract
        // List<Voyage> toMrvPortVoyages = new List<Voyage> {mrvPortToMrvPortVoyages, nonMrvPortToMrvPortVoyages};
        List<LogAbstract> i4InMrvPortLogAbstracts = [];
        foreach (var voyage in i1MrvPortToMrvPortVoyages)
        {
            bool arrivePort = false;
            foreach (var logAbstract in voyage.LogAbstracts)
            {
                // 航次不可能刚开始就是 到港 LogAbstract，所以可以先判断 arrivePort，再判断 logAbstract.Event == LogAbstractEventType.ArrivalEU
                if (arrivePort)
                {
                    i4InMrvPortLogAbstracts.Add(logAbstract);
                }
                if (logAbstract.Event == LogAbstractEventType.ArrivalEU)
                {
                    arrivePort = true;
                }
            }
        }
        foreach (var voyage in i3NonMrvPortToMrvPortVoyages)
        {
            bool arrivePort = false;
            foreach (var logAbstract in voyage.LogAbstracts)
            {
                // 航次不可能刚开始就是 到港 LogAbstract，所以可以先判断 arrivePort，再判断 logAbstract.Event == LogAbstractEventType.ArrivalEU
                if (arrivePort)
                {
                    i4InMrvPortLogAbstracts.Add(logAbstract);
                }
                if (logAbstract.Event == LogAbstractEventType.ArrivalEU)
                {
                    arrivePort = true;
                }
            }
        }
        
        // i4 欧盟港口停靠的各种油耗
        var (i4HfoConsumption, i4LsfoConsumption, i4UlsfoConsumption, i4VlsfoConsumption, i4LfoConsumption, i4MdoMgoConsumption, i4LngConsumption, i4LpgButaneConsumption, i4LpgPropaneConsumption, i4MethanolConsumption) 
            = GetAllFuelTypeFuelConsumptionFromLogAbstracts(i4InMrvPortLogAbstracts);
        
        // i4 欧盟港口停靠的各种气体排放
        (
            float i4TotalAggregatedEmissionsOfCO2EmissionInMrvPort,
            float i4TotalAggregatedEmissionsOfN2OEmissionInMrvPort,
            float i4TotalAggregatedEmissionsOfCH4EmissionInMrvPort
        ) = CalculateTotalAggregatedEmissions(i4InMrvPortLogAbstracts, vesselEeConfig, hasN2oTbmFuel, hasCh4TbmFuel);
        
        worksheet.Cell("D141").Value = i4TotalAggregatedEmissionsOfCO2EmissionInMrvPort.ToString("0.00");
        worksheet.Cell("D142").Value = hasN2oTbmFuel ? "N/A" : i4TotalAggregatedEmissionsOfN2OEmissionInMrvPort.ToString("0.00");
        worksheet.Cell("D143").Value = hasCh4TbmFuel ? "N/A" : i4TotalAggregatedEmissionsOfCH4EmissionInMrvPort.ToString("0.00");

        #endregion i4 欧盟港口停靠

        #region i0 欧盟航次 = i1 + i2 + i3 + i4，相当于 MRV 航次但却不含“非欧盟港口停靠”部分

        // i0 欧盟航次的各种油耗
        var i0HfoConsumption = i1HfoConsumption + i2HfoConsumption + i3HfoConsumption + i4HfoConsumption;
        var i0LsfoConsumption = i1LsfoConsumption + i2LsfoConsumption + i3LsfoConsumption + i4LsfoConsumption;
        var i0UlsfoConsumption = i1UlsfoConsumption + i2UlsfoConsumption + i3UlsfoConsumption + i4UlsfoConsumption;
        var i0VlsfoConsumption = i1VlsfoConsumption + i2VlsfoConsumption + i3VlsfoConsumption + i4VlsfoConsumption;
        var i0LfoConsumption = i1LfoConsumption + i2LfoConsumption + i3LfoConsumption + i4LfoConsumption;
        var i0MdoMgoConsumption = i1MdoMgoConsumption + i2MdoMgoConsumption + i3MdoMgoConsumption + i4MdoMgoConsumption;
        var i0LngConsumption = i1LngConsumption + i2LngConsumption + i3LngConsumption + i4LngConsumption;
        var i0LpgButaneConsumption = i1LpgButaneConsumption + i2LpgButaneConsumption + i3LpgButaneConsumption + i4LpgButaneConsumption;
        var i0LpgPropaneConsumption = i1LpgPropaneConsumption + i2LpgPropaneConsumption + i3LpgPropaneConsumption + i4LpgPropaneConsumption;
        var i0MethanolConsumption = i1MethanolConsumption + i2MethanolConsumption + i3MethanolConsumption + i4MethanolConsumption;
        
        // HFO 消耗
        worksheet.Cell("D87").Value = i0HfoConsumption.ToString("0.00");
        // LSFO (LSHFO) 消耗
        worksheet.Cell("D91").Value = i0LsfoConsumption.ToString("0.00");
        // ULSFO (ULSFO2020) 消耗
        worksheet.Cell("D95").Value = i0UlsfoConsumption.ToString("0.00");
        // VLSFO (VLSFO2020) 消耗
        worksheet.Cell("D99").Value = i0VlsfoConsumption.ToString("0.00");
        // LFO 消耗
        worksheet.Cell("D103").Value = i0LfoConsumption.ToString("0.00");
        // MDO/MGO 消耗
        worksheet.Cell("D107").Value = i0MdoMgoConsumption.ToString("0.00");
        // LNG 消耗
        worksheet.Cell("D111").Value = i0LngConsumption.ToString("0.00");
        // LPG_Butane 消耗
        worksheet.Cell("D115").Value = i0LpgButaneConsumption.ToString("0.00");
        // LPG_Propane 消耗
        worksheet.Cell("D119").Value = i0LpgPropaneConsumption.ToString("0.00");
        // Methanol 消耗
        worksheet.Cell("D123").Value = i0MethanolConsumption.ToString("0.00");
        
        // i0 欧盟航次的各种气体排放
        
        float i0TotalAggregatedEmissionsOfCO2EmissionMrvVoyage = i1TotalAggregatedEmissionsOfCO2EmissionMrvPortToMrvPort +
                                                               i2TotalAggregatedEmissionsOfCO2EmissionMrvPortToNonMrvPort +
                                                               i3TotalAggregatedEmissionsOfCO2EmissionNonMrvPortToMrvPort +
                                                               i4TotalAggregatedEmissionsOfCO2EmissionInMrvPort;
        float i0TotalAggregatedEmissionsOfN2OEmissionMrvVoyage = hasN2oTbmFuel ? 0f : i1TotalAggregatedEmissionsOfN2OEmissionMrvPortToMrvPort +
                                                               i2TotalAggregatedEmissionsOfN2OEmissionMrvPortToNonMrvPort +
                                                               i3TotalAggregatedEmissionsOfN2OEmissionNonMrvPortToMrvPort +
                                                               i4TotalAggregatedEmissionsOfN2OEmissionInMrvPort;
        float i0TotalAggregatedEmissionsOfCH4EmissionMrvVoyage = hasCh4TbmFuel ? 0f : i1TotalAggregatedEmissionsOfCH4EmissionMrvPortToMrvPort +
                                                               i2TotalAggregatedEmissionsOfCH4EmissionMrvPortToNonMrvPort +
                                                               i3TotalAggregatedEmissionsOfCH4EmissionNonMrvPortToMrvPort +
                                                               i4TotalAggregatedEmissionsOfCH4EmissionInMrvPort;
        
        worksheet.Cell("D125").Value = i0TotalAggregatedEmissionsOfCO2EmissionMrvVoyage.ToString("0.00");
        worksheet.Cell("D126").Value = hasN2oTbmFuel ? "N/A" : i0TotalAggregatedEmissionsOfN2OEmissionMrvVoyage.ToString("0.00");
        worksheet.Cell("D127").Value = hasCh4TbmFuel ? "N/A" : i0TotalAggregatedEmissionsOfCH4EmissionMrvVoyage.ToString("0.00");

        #endregion i0 欧盟航次 = i1 + i2 + i3 + i4（不含非欧盟港口停靠部分）
        
        
        var mrvLogAbstracts = mrvVoyages.SelectMany(mv => mv.LogAbstracts).ToList();
        

        #region DISTANCE TRAVELLED, TIME SPENT AT SEA AND TRANSPORT WORK 航行的距离，在海上和运输工作中花费的时间

        // 报告期内的（欧盟航次）：航行距离（不含抛锚期间的漂移距离）
        List<LogAbstract> mrvVoyagesLogAbstractsWithoutAnchoring = [];
        foreach (var voyage in mrvVoyages)
        {
            bool inAnchoring = false;
            foreach (var logAbstract in voyage.LogAbstracts)
            {
                // 检查当前 logAbstract 是否表示锚泊的开始
                if (logAbstract.Event == LogAbstractEventType.BeginAnchoringOrDrifting)
                {
                    inAnchoring = true;
                    continue;  // 跳过此条LogAbstract，不加入到列表中
                }

                // 如果当前不在锚泊期间，则添加到列表中
                if (!inAnchoring)
                {
                    mrvVoyagesLogAbstractsWithoutAnchoring.Add(logAbstract);
                }

                // 检查当前 logAbstract 是否表示锚泊的结束
                if (logAbstract.Event == LogAbstractEventType.EndAnchoringOrDrifting)
                {
                    inAnchoring = false;
                    // 结束锚泊的记录需要被加入到列表
                    mrvVoyagesLogAbstractsWithoutAnchoring.Add(logAbstract);
                }
            }
        }
        worksheet.Cell("D166").Value = mrvVoyagesLogAbstractsWithoutAnchoring.Sum(la => la.Distance).ToString("0.00");
        
        // 报告期内的（欧盟航次）：在航时长（不含锚泊和靠港的期间的时长）
        TimeSpan underWayDuration = TimeSpan.Zero;
        // 只考虑有离港事件的航次
        foreach (var voyage in mrvVoyages.Where(v => v.StartDepartureLogAbstract is not null))
        {
            bool inAnchoring = false;
            bool inPort = false;
            DateTime? anchoringStart = null;
            DateTime? portStart = null;
            TimeSpan anchoringDuration = TimeSpan.Zero;
            TimeSpan portDuration = TimeSpan.Zero;
            DateTime lastTimeStamp = voyage.StartDepartureLogAbstract!.Ts; // 航次日志是按时间顺序排序的

            foreach (var logAbstract in voyage.LogAbstracts)
            {
                // 检查锚泊开始
                if (logAbstract.Event == LogAbstractEventType.BeginAnchoringOrDrifting && !inAnchoring)
                {
                    inAnchoring = true;
                    anchoringStart = logAbstract.Ts;
                    continue;
                }

                // 检查靠港开始
                if (logAbstract.Event == LogAbstractEventType.ArrivalEU && !inPort)
                {
                    inPort = true;
                    portStart = logAbstract.Ts;
                    continue;
                }

                // 计算锚泊时长
                if (logAbstract.Event == LogAbstractEventType.EndAnchoringOrDrifting && inAnchoring)
                {
                    if (anchoringStart.HasValue)
                    {
                        anchoringDuration += logAbstract.Ts - anchoringStart.Value;
                    }

                    inAnchoring = false;
                }

                // 计算靠港时长
                if (logAbstract.Event == LogAbstractEventType.DepartureEU && inPort)
                {
                    if (portStart.HasValue)
                    {
                        portDuration += logAbstract.Ts - portStart.Value;
                    }

                    inPort = false;
                }

                lastTimeStamp = logAbstract.Ts; // 更新最后的时间戳
            }

            // 计算航次总时长
            TimeSpan totalDuration = lastTimeStamp - voyage.StartDepartureLogAbstract.Ts;

            // 计算在航时长
            underWayDuration = totalDuration - anchoringDuration - portDuration;
        }
        worksheet.Cell("D168").Value = underWayDuration.TotalHours.ToString("0.00");
        
        // 每个航次的运输功之和（运输功 = 航行距离 * 载货量）
        float totalTransportWork = 0f;
        foreach (var voyage in mrvVoyages)
        {
            foreach (var logAbstract in voyage.LogAbstracts)
            {
                totalTransportWork += logAbstract.Distance * logAbstract.GetMCargo(vesselEeConfig.VesselType);
            }
        }
        worksheet.Cell("D170").Value = totalTransportWork.ToString("0.00");
        
        #endregion DISTANCE TRAVELLED, TIME SPENT AT SEA AND TRANSPORT WORK 航行的距离，在海上和运输工作中花费的时间

        #region ENERGY EFFICIENCY

        // 报告期内的（欧盟航次i0）：每个航次的油耗之和/每个航次的航行距离 (kg/nm)
        var mrvVoyagesFuelConsumptionPerDistance = LogAbstractFormulas.CSNullable(mrvLogAbstracts, vesselEeConfig.FuelTypes);
        if (mrvVoyagesFuelConsumptionPerDistance is not null)
        {
            // 将单位转换为 kg/nm
            worksheet.Cell("D177").Value = (mrvVoyagesFuelConsumptionPerDistance.Value * 1_000f).ToString("0.00");
        }

        // 报告期内的（欧盟航次i0）：每个航次的油耗之和/每个航次的运输功之和，运输功=航行距离*载货量 (g/(t·nm))
        var mrvVoyagesFuelConsumptionPerTransportWork =
            LogAbstractFormulas.CPNullable(mrvLogAbstracts, vesselEeConfig.FuelTypes, vesselEeConfig.VesselType);
        if (mrvVoyagesFuelConsumptionPerTransportWork is not null)
        {
            // 单位本身就为 g/(t·nm)，无需再转换
            worksheet.Cell("D178").Value = mrvVoyagesFuelConsumptionPerTransportWork.Value.ToString("0.00");
        }
        
        // 报告期内的（欧盟航次i0）：每个航次的二氧化碳排放量/每个航次的航行距离，二氧化碳排放量=各油品消耗量*相应的碳转化系数 (kg CO2/nm)
        var mrvVoyagesCO2EmissionsPerDistance = LogAbstractFormulas.ESNullable(mrvLogAbstracts, vesselEeConfig.FuelTypes);
        if (mrvVoyagesCO2EmissionsPerDistance is not null)
        {
            // 将单位转换为 kg CO2/nm
            worksheet.Cell("D179").Value = (mrvVoyagesCO2EmissionsPerDistance.Value * 1_000f).ToString("0.00");
        }
        
        // 报告期内的（欧盟航次i0）：每个航次的二氧化碳当量排放量/每个航次的航行距离，二氧化碳当量排放量=各油品消耗量*相应的碳当量转化系数 (kg CO2eq/nm)
        var mrvVoyagesCO2EquivalentEmissionsPerDistance = LogAbstractFormulas.ESNullableForMRV(mrvLogAbstracts, vesselEeConfig.FuelTypes, gwpN2O, gwpCH4);
        if (mrvVoyagesCO2EquivalentEmissionsPerDistance is not null)
        {
            // 将单位转换为 kg CO2eq/nm
            worksheet.Cell("D180").Value = (mrvVoyagesCO2EquivalentEmissionsPerDistance.Value * 1_000f).ToString("0.00");
        }
        
        // 报告期内的（欧盟航次i0）：每个航次的二氧化碳排放量/每个航次的运输功之和，运输功=航行距离*载货量 (g CO2/(t·nm))
        var mrvVoyagesCO2EmissionsPerTransportWork =
            LogAbstractFormulas.ECNullable(mrvLogAbstracts, vesselEeConfig.FuelTypes, vesselEeConfig.VesselType);
        if (mrvVoyagesCO2EmissionsPerTransportWork is not null)
        {
            // 单位本身就为 g CO2/(t·nm)，无需再转换
            worksheet.Cell("D181").Value = mrvVoyagesCO2EmissionsPerTransportWork.Value.ToString("0.00");
        }
        
        // 报告期内的（欧盟航次i0）：每个航次的二氧化碳当量排放量/每个航次的运输功之和，运输功=航行距离*载货量，二氧化碳当量排放量=各油品消耗量*相应的碳当量转化系数 (g CO2eq/(t·nm))
        var mrvVoyagesCO2EquivalentEmissionsPerTransportWork =
            LogAbstractFormulas.ECNullableForMRV(mrvLogAbstracts, vesselEeConfig.FuelTypes, vesselEeConfig.VesselType, gwpN2O, gwpCH4);
        if (mrvVoyagesCO2EquivalentEmissionsPerTransportWork is not null)
        {
            // 单位本身就为 g CO2eq/(t·nm)，无需再转换
            worksheet.Cell("D182").Value = mrvVoyagesCO2EquivalentEmissionsPerTransportWork.Value.ToString("0.00");
        }
        
        #endregion ENERGY EFFICIENCY

        #region GREENHOUSE GAS EMITTED AND OTHER RELEVANT INFORMATION 温室气体排放及其他相关资料

        // 以下的值前面都已经计算过了
        
        // 2. 报告期内的成员国港口间航次（i1）:
        // Total aggregated emissions of CO2 Emission (t CO2eq)
        worksheet.Cell("D225").Value = i1TotalAggregatedEmissionsOfCO2EmissionMrvPortToMrvPort.ToString("0.00");
        // Total aggregated emissions of N2O Emission (t CO2eq)
        worksheet.Cell("D226").Value = hasN2oTbmFuel ? "N/A" : i1TotalAggregatedEmissionsOfN2OEmissionMrvPortToMrvPort.ToString("0.00");
        // Total aggregated emissions of CH4 Emission (t CO2eq)
        worksheet.Cell("D227").Value = hasCh4TbmFuel ? "N/A" : i1TotalAggregatedEmissionsOfCH4EmissionMrvPortToMrvPort.ToString("0.00");

        // 3. 报告期内的驶离成员国港口航次（i2）:
        // Total aggregated emissions of CO2 Emission (t CO2eq)
        worksheet.Cell("D229").Value = i2TotalAggregatedEmissionsOfCO2EmissionMrvPortToNonMrvPort.ToString("0.00");
        // Total aggregated emissions of N2O Emission (t CO2eq)
        worksheet.Cell("D230").Value = hasN2oTbmFuel ? "N/A" : i2TotalAggregatedEmissionsOfN2OEmissionMrvPortToNonMrvPort.ToString("0.00");
        // Total aggregated emissions of CH4 Emission (t CO2eq)
        worksheet.Cell("D231").Value = hasCh4TbmFuel ? "N/A" : i2TotalAggregatedEmissionsOfCH4EmissionMrvPortToNonMrvPort.ToString("0.00");
        
        // 4. 报告期内的到达成员国港口航次（i3）:
        // Total aggregated emissions of CO2 Emission (t CO2eq)
        worksheet.Cell("D233").Value = i3TotalAggregatedEmissionsOfCO2EmissionNonMrvPortToMrvPort.ToString("0.00");
        // Total aggregated emissions of N2O Emission (t CO2eq)
        worksheet.Cell("D234").Value = hasN2oTbmFuel ? "N/A" : i3TotalAggregatedEmissionsOfN2OEmissionNonMrvPortToMrvPort.ToString("0.00");
        // Total aggregated emissions of CH4 Emission (t CO2eq)
        worksheet.Cell("D235").Value = hasCh4TbmFuel ? "N/A" : i3TotalAggregatedEmissionsOfCH4EmissionNonMrvPortToMrvPort.ToString("0.00");

        // 5. 报告期内的欧盟港口停靠（i4）:
        // Total aggregated emissions of CO2 Emission (t CO2eq)
        worksheet.Cell("D237").Value = i4TotalAggregatedEmissionsOfCO2EmissionInMrvPort.ToString("0.00");
        // Total aggregated emissions of N2O Emission (t CO2eq)
        worksheet.Cell("D238").Value = hasN2oTbmFuel ? "N/A" : i4TotalAggregatedEmissionsOfN2OEmissionInMrvPort.ToString("0.00");
        // Total aggregated emissions of CH4 Emission (t CO2eq)
        worksheet.Cell("D239").Value = hasCh4TbmFuel ? "N/A" : i4TotalAggregatedEmissionsOfCH4EmissionInMrvPort.ToString("0.00");
        
        // 6. 报告期内的欧盟航次（i0）:
        // Total aggregated emissions of CO2 Emission (t CO2eq)
        worksheet.Cell("D241").Value = i0TotalAggregatedEmissionsOfCO2EmissionMrvVoyage.ToString("0.00");
        // Total aggregated emissions of N2O Emission (t CO2eq)
        worksheet.Cell("D242").Value = hasN2oTbmFuel ? "N/A" : i0TotalAggregatedEmissionsOfN2OEmissionMrvVoyage.ToString("0.00");
        // Total aggregated emissions of CH4 Emission (t CO2eq)
        worksheet.Cell("D243").Value = hasCh4TbmFuel ? "N/A" : i0TotalAggregatedEmissionsOfCH4EmissionMrvVoyage.ToString("0.00");
        
        // 7. 与上面的相同：
        // Total aggregated emissions of CO2 Emission (t CO2eq)
        worksheet.Cell("D245").Value = i0TotalAggregatedEmissionsOfCO2EmissionMrvVoyage.ToString("0.00");
        // Total aggregated emissions of N2O Emission (t CO2eq)
        worksheet.Cell("D246").Value = hasN2oTbmFuel ? "N/A" : i0TotalAggregatedEmissionsOfN2OEmissionMrvVoyage.ToString("0.00");
        // Total aggregated emissions of CH4 Emission (t CO2eq)
        worksheet.Cell("D247").Value = hasCh4TbmFuel ? "N/A" : i0TotalAggregatedEmissionsOfCH4EmissionMrvVoyage.ToString("0.00");

        // 8.
        // Total aggregated emissions of CO2 Emission (t CO2eq)
        float d249 = (i1TotalAggregatedEmissionsOfCO2EmissionMrvPortToMrvPort +
                    i2TotalAggregatedEmissionsOfCO2EmissionMrvPortToNonMrvPort * 0.5f +
                    i3TotalAggregatedEmissionsOfCO2EmissionNonMrvPortToMrvPort * 0.5f +
                    i4TotalAggregatedEmissionsOfCO2EmissionInMrvPort);
        worksheet.Cell("D249").Value = d249.ToString("0.00");
        // Total aggregated emissions of N2O Emission (t CO2eq)
        float? d250 = hasN2oTbmFuel
            ? null
            : (i1TotalAggregatedEmissionsOfN2OEmissionMrvPortToMrvPort +
               i2TotalAggregatedEmissionsOfN2OEmissionMrvPortToNonMrvPort * 0.5f +
               i3TotalAggregatedEmissionsOfN2OEmissionNonMrvPortToMrvPort * 0.5f +
               i4TotalAggregatedEmissionsOfN2OEmissionInMrvPort);
        worksheet.Cell("D250").Value = d250 is null ? "N/A" : d250.Value.ToString("0.00");
        // Total aggregated emissions of CH4 Emission (t CO2eq)
        float? d251 = hasCh4TbmFuel ? null : (i1TotalAggregatedEmissionsOfCH4EmissionMrvPortToMrvPort + 
                                              i2TotalAggregatedEmissionsOfCH4EmissionMrvPortToNonMrvPort * 0.5f +
                                              i3TotalAggregatedEmissionsOfCH4EmissionNonMrvPortToMrvPort * 0.5f +
                                              i4TotalAggregatedEmissionsOfCH4EmissionInMrvPort);
        worksheet.Cell("D251").Value = d251 is null ? "N/A" : d251.Value.ToString("0.00");
        
        // 9. 上面对应的 8
        worksheet.Cell("D253").Value = d249.ToString("0.00");
        worksheet.Cell("D254").Value = d250 is null ? "N/A" : d250.Value.ToString("0.00");
        worksheet.Cell("D255").Value = d251 is null ? "N/A" : d251.Value.ToString("0.00");
        
        // 10. 上面对应的 8
        worksheet.Cell("D257").Value = d249.ToString("0.00");
        worksheet.Cell("D258").Value = d250 is null ? "N/A" : d250.Value.ToString("0.00");
        worksheet.Cell("D259").Value = d251 is null ? "N/A" : d251.Value.ToString("0.00");
        
        // 11. 上面对应的 8
        worksheet.Cell("D261").Value = d249.ToString("0.00");
        worksheet.Cell("D262").Value = d250 is null ? "N/A" : d250.Value.ToString("0.00");
        worksheet.Cell("D263").Value = d251 is null ? "N/A" : d251.Value.ToString("0.00");
        
        // 12. 
        /*
         2024年的排放数据：上面对应的 8* 系数0.4
         2025年的排放数据：上面对应的 8* 系数0.7
         2026年及以后的排放数据：上面对应的 8* 系数1
        */
        
        int yearNumber = start.Year;
        float? coefficient = null;
        // TODO: 2024 年，包括 2024 年之前的，先设置为都为 0.4，为了测试
        if (yearNumber <= 2024)
        {
            coefficient = 0.4f;
        }
        else if (yearNumber == 2025)
        {
            coefficient = 0.7f;
        }
        else if (yearNumber >= 2026)
        {
            coefficient = 1f;
        }

        // Total aggregated emissions of CO2 Emission (t CO2eq)
        worksheet.Cell("D265").Value = coefficient is null ? "N/A" : (d249 * coefficient.Value).ToString("0.00");
        // Total aggregated emissions of N2O Emission (t CO2eq)
        worksheet.Cell("D266").Value = coefficient is null || d250 is null ? "N/A" : (d250.Value * coefficient.Value).ToString("0.00");
        // Total aggregated emissions of CH4 Emission (t CO2eq)
        worksheet.Cell("D267").Value = coefficient is null || d251 is null ? "N/A" : (d251.Value * coefficient.Value).ToString("0.00");
        
        #endregion GREENHOUSE GAS EMITTED AND OTHER RELEVANT INFORMATION 温室气体排放及其他相关资料

        var stream = new MemoryStream();
        workbook.SaveAs(stream);
        stream.Position = 0;

        return stream;
    }

    private static (float hfoConsumption, float lsfoConsumption, float ulsfoConsumption, float vlsfoConsumption, float lfoConsumption, float mdoMgoConsumption, float lngConsumption, float lpgButaneConsumption, float lpgPropaneConsumption, float methanolConsumption) 
        GetAllFuelTypeFuelConsumptionFromLogAbstracts(List<LogAbstract> logAbstracts)
    {
        // HFO 消耗
        var hfoConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.HFO);
        // LSFO (LSHFO) 消耗
        var lsfoConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.LSHFO);
        // ULSFO (ULSFO2020) 消耗
        var ulsfoConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.ULSFO2020);
        // VLSFO (VLSFO2020) 消耗
        var vlsfoConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.VLSFO2020);
        // LFO 消耗
        var lfoConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.LFO);
        // MDO/MGO 消耗
        var mdoMgoConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.MDO) +
                                  logAbstracts.GetTotalConsumptionByFuelType(FuelType.MGO);
        // LNG 消耗
        var lngConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.LNG);
        // LPG_Butane 消耗
        var lpgButaneConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.LPGB);
        // LPG_Propane 消耗
        var lpgPropaneConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.LPGP);
        // Methanol 消耗
        var methanolConsumption = logAbstracts.GetTotalConsumptionByFuelType(FuelType.Methanol);

        return (hfoConsumption, lsfoConsumption, ulsfoConsumption, vlsfoConsumption, lfoConsumption, 
            mdoMgoConsumption, lngConsumption, lpgButaneConsumption, lpgPropaneConsumption, methanolConsumption);
    }

    private static List<LogAbstract> GetSailingLogAbstractsFromVoyages(List<Voyage> voyages,
        HashSet<LogAbstractEventType> arrivalEventTypes)
    {
        List<LogAbstract> result = [];
        
        foreach (var voyage in voyages)
        {
            result.AddRange(GetSailingLogAbstractsFromVoyage(voyage, arrivalEventTypes));
        }

        return result;
    }

    private static List<LogAbstract> GetSailingLogAbstractsFromVoyage(Voyage voyage,
        HashSet<LogAbstractEventType> arrivalEventTypes)
    {
        List<LogAbstract> result = [];
        
        foreach (var logAbstract in voyage.LogAbstracts)
        {
            result.Add(logAbstract);
            if (arrivalEventTypes.Contains(logAbstract.Event))
            {
                break;
            }
        }

        return result;
    }


    private static (float TotalAggregatedEmissionsOfCO2Emission, float TotalAggregatedEmissionsOfN2OEmission, float
        TotalAggregatedEmissionsOfCH4Emission) CalculateTotalAggregatedEmissions(
            List<LogAbstract> logAbstracts, VesselEeConfig vesselEeConfig, bool hasN2oTbmFuel,
            bool hasCh4TbmFuel)
    {
        const float gwpCO2 = 1.0f;
        const float gwpN2O = 265.0f;
        const float gwpCH4 = 28.0f;

        float totalAggregatedEmissionsOfCO2Emission = 0;
        float totalAggregatedEmissionsOfN2OEmission = 0;
        float totalAggregatedEmissionsOfCH4Emission = 0;

        foreach (var fuelType in vesselEeConfig.FuelTypes)
        {
            totalAggregatedEmissionsOfCO2Emission += logAbstracts.GetTotalConsumptionByFuelType(fuelType) *
                                                     FuelCfMapper.GetFuelCfCO2(fuelType) * gwpCO2;
            if (!hasN2oTbmFuel)
            {
                totalAggregatedEmissionsOfN2OEmission += logAbstracts.GetTotalConsumptionByFuelType(fuelType) *
                                                         FuelCfMapper.GetFuelCfN2O(fuelType) * gwpN2O;
            }

            if (!hasCh4TbmFuel)
            {
                totalAggregatedEmissionsOfCH4Emission += logAbstracts.GetTotalConsumptionByFuelType(fuelType) *
                                                         FuelCfMapper.GetFuelCfCH4(fuelType) * gwpCH4;
            }
        }

        return (totalAggregatedEmissionsOfCO2Emission, totalAggregatedEmissionsOfN2OEmission,
            totalAggregatedEmissionsOfCH4Emission);
    }

    public async Task<MemoryStream> ExportLogAbstractsAsync(Guid vesselId, DateTime startDateTime, DateTime endDateTime)
    {
        var vesselEeConfig = await _vesselEeConfigDomainService.GetByVesselIdAsync(vesselId);
        if (vesselEeConfig is null)
        {
            throw new UserFriendlyException(L[PHMEEEnergyEfficiencyErrorCodes.VesselConfigNotFound],
                PHMEEEnergyEfficiencyErrorCodes.VesselConfigNotFound);
        }
        
        var (start, end) = QueryDateTimeUtils.ProcessForDateRangeQuery(startDateTime, endDateTime);
        
        // 前闭后开，与 LogAbstract 列表页的查询方式一致
        var logAbstracts = await _logAbstractRepository.GetListClosedOpenAsync(vesselId, start, end);
        
        var dtos = ObjectMapper.Map<List<LogAbstract>, List<LogAbstractDto>>(logAbstracts);
        
        return LogAbstractExportToCsv(dtos);
    }
    
    /// <summary>
    /// LogAbstract 导出 Csv，DateTime、DateOnly 和 TimeOnly 都将格式化。
    /// </summary>
    /// <param name="items"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    private static MemoryStream LogAbstractExportToCsv<T>(List<T> items)
    {
        var type = typeof(T);
        var properties = type.GetProperties();
        var memoryStream = new MemoryStream();
        var writer = new StreamWriter(memoryStream, Encoding.UTF8);

        // 写入标题（属性名）
        var header = string.Join(",", properties.Select(p => p.Name));
        writer.WriteLine(header);

        // 写入每个对象的属性值
        foreach (var item in items)
        {
            var line = string.Join(",", properties.Select(p =>
            {
                var value = p.GetValue(item, null);
                if (value == null)
                {
                    return string.Empty;
                }
                
                // 如果是 DateTime 类型的属性，则转为字符串
                if (p.PropertyType == typeof(DateTime))
                {
                    return ((DateTime)value).ToString("yyyy-MM-ddTHH:mm:ss.fffZ");
                }

                if (p.PropertyType == typeof(DateOnly))
                {
                    return ((DateOnly)value).ToString("yyyy-MM-dd");
                }

                if (p.PropertyType == typeof(TimeOnly))
                {
                    return ((TimeOnly)value).ToString("HH:mm");
                }

                return p.GetValue(item, null);
            }));
            writer.WriteLine(line);
        }

        writer.Flush();
        memoryStream.Position = 0; // 重置流位置

        return memoryStream;
    }
}